package com.cloud.force.common.utils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import com.cloud.force.api.entity.TaskListField;
import com.cloud.force.common.consts.ForceConsts;
import com.cloud.force.common.exception.BizException;

public class SqlParserHelper {

	/**
	 * 通过指定sql获取列名称、数据类型相关信息
	 * 
	 * @param sql
	 * @param jdbcTemplate
	 * @return
	 */
	public static List<TaskListField> listFieldInfoBySQL(String sql, JdbcTemplate jdbcTemplate) {
		List<TaskListField> result = new ArrayList<TaskListField>();
		try {
			sql = replaceSQLEL2NULL(sql); //替换sql中的EL表达式为NULL来保障SQL语法的正确性
			StringBuffer sqlBuffer = new StringBuffer("select * from (").append(sql).append(") _temp_ where 1 = 2");
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sqlBuffer.toString());
			SqlRowSetMetaData metaData = rowSet.getMetaData();
			int columnCount = metaData.getColumnCount();

			for (int i = 1; i <= columnCount; i++) {
				TaskListField field = new TaskListField();
				// 名称，默认显示为列名
				field.setFieldName(metaData.getColumnName(i));
				field.setColumnName(metaData.getColumnName(i));
				field.setVisibleFlag(1);
				field.setSearchFlag(0);
				field.setDisplayOrder(i);
				String columnClassName = metaData.getColumnClassName(i);
				if ("java.lang.String".equals(columnClassName)) {
					field.setFieldType(ForceConsts.DATA_TYPE_TEXT);
					field.setFieldTypeName(ForceConsts.DATA_TYPE_TEXT_NAME);
				} else if ("java.lang.Long".equals(columnClassName) || "java.lang.Integer".equals(columnClassName)
						|| "java.lang.Float".equals(columnClassName) || "java.lang.Double".equals(columnClassName)
						|| "java.math.BigDecimal".equals(columnClassName)
						|| "java.math.Decimal".equals(columnClassName)) {
					field.setFieldType(ForceConsts.DATA_TYPE_NUMBER);
					field.setFieldTypeName(ForceConsts.DATA_TYPE_NUMBER_NAME);
				} else if ("java.util.Date".equals(columnClassName) || "java.sql.Date".equals(columnClassName)
						|| "java.sql.Timestamp".equals(columnClassName)) {
					field.setFieldType(ForceConsts.DATA_TYPE_DATE);
					field.setFieldTypeName(ForceConsts.DATA_TYPE_DATE_NAME);
					if ("java.sql.Timestamp".equals(columnClassName)) {
						field.setFieldFormat(DateHelper.TIME_FORMAT);
					} else {
						field.setFieldFormat(DateHelper.DATE_FORMAT);
					}
				}
				result.add(field);
			}
		} catch (BadSqlGrammarException e) {
			throw new BizException(500, "SQL语法错误，请检查语法配置！");
		}
		return result;
	}

	/**
	 * 动态替换文本中的EL表达式
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static String dynamicReplaceSQLEL(String sql, Map<String, String> params) {
		for (String key : params.keySet()) {
			sql = sql.replaceAll("\\$\\{(\\s?)+(" + key + ")(\\s?)+\\}", params.get(key));
		}
		return sql;
	}
	
	
	/**
	 * 替换文本中的EL表达式为空
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static String replaceSQLEL2NULL(String sql) {
		return sql.replaceAll("\\$\\{(\\s?)+(.*?)(\\s?)+\\}", "null");
	}
}
